package android.songfinder;

import java.util.ArrayList;
import java.util.LinkedList;
import java.util.List;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteOpenHelper;

public class SongListDatabase extends SQLiteOpenHelper {

	public SongListDatabase(Context context) {
        super(context, "SongFinderDatabase", null, 1);
    }

	@Override
	public void onCreate(SQLiteDatabase db) {
		db.execSQL("create table PlayLists (_id integer primary key autoincrement, ListName TEXT not null)");
		db.execSQL("create table PlayListSongs (_id integer primary key autoincrement, ListId integer not null, SongPath TEXT not null, foreign key(ListId) references PlayLists(_id))");
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		// TODO Auto-generated method stub
		
	}
	
	public void createListAndAddSong(String listName, String songPath) {
		
		SQLiteDatabase db=this.getWritableDatabase();
		try {
			db.beginTransaction();
			ContentValues insertValues = new ContentValues();
			insertValues.put("ListName", listName);
			long newId = db.insert("PlayLists", null, insertValues);
			
			ContentValues insertValues2 = new ContentValues();
			insertValues2.put("ListId", newId);
			insertValues2.put("SongPath", songPath);
			db.insert("PlayListSongs", null, insertValues2);
			
			db.setTransactionSuccessful();
		} finally {
			db.endTransaction();
		}
	}
	
	public boolean checkListTitleExists(String listName) {
		   SQLiteDatabase db=this.getReadableDatabase();
		   Cursor c=db.query("PlayLists", new String[]{"_id as _id"},
			"ListName=?", new String[]{listName}, null, null, null);
		   //Cursor c=db.rawQuery("SELECT "+colDeptID+" as _id FROM "+deptTable+" 
		   //WHERE "+colDeptName+"=?", new String []{Dept});
		   if (c.moveToFirst())
			   return true;
		   return false;

	}
	
	public List<Integer> GetListIdsForSong(String songPath) {
		List<Integer> intList = new ArrayList<Integer>();
		SQLiteDatabase db=this.getReadableDatabase();
		
		return intList;
	}
	
	public List<ListEntry> GetLists(String songPathExclude) {
		LinkedList<ListEntry> outList = new LinkedList<ListEntry>();
		
		   SQLiteDatabase db=this.getReadableDatabase();
		   //Cursor query = db.rawQuery("select _id as _id", "ListName as ListName from PlayLists", new String[]{"kk"});
		   Cursor c=db.query("PlayLists", new String[]{"_id as _id", "ListName as ListName"},
			"", new String[]{}, null, null, null);
		   
		   if  (c.moveToFirst()) {
		        do {
		            String listName = c.getString(c.getColumnIndex("ListName"));
		            int id = c.getInt(c.getColumnIndex("_id"));
		            
		            ListEntry entry = new ListEntry();
		            entry.setListName(listName);
		            entry.setId(id);
		            outList.add(entry);
		        }while (c.moveToNext());
		    }

		   return outList;
	}
	
	public List<ListEntry> GetLists() {
		LinkedList<ListEntry> outList = new LinkedList<ListEntry>();
		
		   SQLiteDatabase db=this.getReadableDatabase();
		   Cursor c=db.query("PlayLists", new String[]{"_id as _id", "ListName as ListName"},
			"", new String[]{}, null, null, null);
		   
		   if  (c.moveToFirst()) {
		        do {
		            String listName = c.getString(c.getColumnIndex("ListName"));
		            int id = c.getInt(c.getColumnIndex("_id"));
		            ListEntry entry = new ListEntry();
		            entry.setListName(listName);
		            entry.setId(id);
		            outList.add(entry);
		        }while (c.moveToNext());
		    }

		   return outList;
		
	}

}
